/**
 * SQL示例数据库
 * 为不同的节点类型提供相应的SQL查询示例
 */
class SqlExampleGenerator {
    constructor() {
        this.examples = this.initializeExamples();
    }

    /**
     * 初始化SQL示例数据
     */
    initializeExamples() {
        return {
            // 表访问操作
            'table_scan': {
                title: '全表扫描',
                description: '当没有合适的索引可用时，MySQL会进行全表扫描',
                examples: [
                    {
                        name: '无WHERE条件查询',
                        sql: `-- 全表扫描示例：查询所有用户
SELECT * FROM users;`,
                        explain_sql: `EXPLAIN SELECT * FROM users;`
                    },
                    {
                        name: '无索引字段条件',
                        sql: `-- 全表扫描示例：按无索引字段查询
SELECT * FROM orders WHERE create_time > '2024-01-01';`,
                        explain_sql: `EXPLAIN SELECT * FROM orders WHERE create_time > '2024-01-01';`
                    },
                    {
                        name: '函数导致索引失效',
                        sql: `-- 全表扫描示例：函数导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;`,
                        explain_sql: `EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;`
                    }
                ]
            },

            'index_scan': {
                title: '索引扫描',
                description: '使用索引扫描来快速定位数据',
                examples: [
                    {
                        name: '主键查询',
                        sql: `-- 索引扫描示例：主键查询
SELECT * FROM users WHERE id = 123;`,
                        explain_sql: `EXPLAIN SELECT * FROM users WHERE id = 123;`
                    },
                    {
                        name: '唯一索引查询',
                        sql: `-- 索引扫描示例：唯一索引查询
SELECT * FROM users WHERE email = 'user@example.com';`,
                        explain_sql: `EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';`
                    },
                    {
                        name: '复合索引查询',
                        sql: `-- 索引扫描示例：复合索引查询
SELECT * FROM orders WHERE user_id = 123 AND status = 'active';`,
                        explain_sql: `EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'active';`
                    }
                ]
            },

            'index_lookup': {
                title: '索引查找',
                description: '通过索引快速定位到具体的数据行',
                examples: [
                    {
                        name: '主键精确查找',
                        sql: `-- 索引查找示例：主键精确查找
SELECT name, email FROM users WHERE id = 1001;`,
                        explain_sql: `EXPLAIN SELECT name, email FROM users WHERE id = 1001;`
                    },
                    {
                        name: '外键关联查询',
                        sql: `-- 索引查找示例：外键关联查询
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON u.id = o.user_id 
WHERE o.id = 5001;`,
                        explain_sql: `EXPLAIN SELECT o.*, u.name FROM orders o JOIN users u ON u.id = o.user_id WHERE o.id = 5001;`
                    }
                ]
            },

            // 连接操作
            'nested_loop_join': {
                title: '嵌套循环连接',
                description: 'MySQL最常用的连接算法，适合小表连接',
                examples: [
                    {
                        name: '内连接',
                        sql: `-- 嵌套循环连接示例：用户订单查询
SELECT u.name, o.total_amount, o.create_time
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';`,
                        explain_sql: `EXPLAIN SELECT u.name, o.total_amount, o.create_time FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';`
                    },
                    {
                        name: '左连接',
                        sql: `-- 嵌套循环连接示例：用户及其订单（包含无订单用户）
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;`,
                        explain_sql: `EXPLAIN SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;`
                    }
                ]
            },

            'hash_join': {
                title: '哈希连接',
                description: 'MySQL 8.0+支持的高效连接算法，适合大表连接',
                examples: [
                    {
                        name: '大表连接',
                        sql: `-- 哈希连接示例：大表连接查询
SELECT /*+ HASH_JOIN(u, o) */ u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.create_time >= '2024-01-01';`,
                        explain_sql: `EXPLAIN FORMAT=TREE SELECT /*+ HASH_JOIN(u, o) */ u.name, o.total_amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.create_time >= '2024-01-01';`
                    }
                ]
            },

            // 排序和分组
            'sort': {
                title: '排序操作',
                description: '对查询结果进行排序，可能使用文件排序或索引排序',
                examples: [
                    {
                        name: '文件排序',
                        sql: `-- 排序示例：按非索引字段排序（文件排序）
SELECT * FROM orders 
WHERE status = 'completed'
ORDER BY total_amount DESC;`,
                        explain_sql: `EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY total_amount DESC;`
                    },
                    {
                        name: '索引排序',
                        sql: `-- 排序示例：按索引字段排序（索引排序）
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10;`,
                        explain_sql: `EXPLAIN SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;`
                    },
                    {
                        name: '复合排序',
                        sql: `-- 排序示例：多字段排序
SELECT user_id, total_amount, create_time
FROM orders 
ORDER BY user_id ASC, create_time DESC;`,
                        explain_sql: `EXPLAIN SELECT user_id, total_amount, create_time FROM orders ORDER BY user_id ASC, create_time DESC;`
                    }
                ]
            },

            'group_by': {
                title: '分组操作',
                description: '按指定字段对数据进行分组',
                examples: [
                    {
                        name: '简单分组',
                        sql: `-- 分组示例：按用户统计订单数
SELECT user_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
FROM orders 
GROUP BY user_id;`,
                        explain_sql: `EXPLAIN SELECT user_id, COUNT(*) as order_count, SUM(total_amount) as total_spent FROM orders GROUP BY user_id;`
                    },
                    {
                        name: '分组过滤',
                        sql: `-- 分组示例：统计活跃用户（订单数>5）
SELECT user_id, COUNT(*) as order_count
FROM orders 
GROUP BY user_id 
HAVING COUNT(*) > 5;`,
                        explain_sql: `EXPLAIN SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5;`
                    }
                ]
            },

            'aggregate': {
                title: '聚合函数',
                description: '执行COUNT、SUM、AVG等聚合计算',
                examples: [
                    {
                        name: '统计聚合',
                        sql: `-- 聚合示例：订单统计信息
SELECT 
    COUNT(*) as total_orders,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value,
    MAX(total_amount) as max_order,
    MIN(total_amount) as min_order
FROM orders 
WHERE create_time >= '2024-01-01';`,
                        explain_sql: `EXPLAIN SELECT COUNT(*), SUM(total_amount), AVG(total_amount), MAX(total_amount), MIN(total_amount) FROM orders WHERE create_time >= '2024-01-01';`
                    }
                ]
            },

            // 子查询和派生表
            'subquery': {
                title: '子查询',
                description: '嵌套在主查询中的查询语句',
                examples: [
                    {
                        name: '相关子查询',
                        sql: `-- 子查询示例：查找每个用户的最新订单
SELECT u.name, u.email,
    (SELECT MAX(o.create_time) 
     FROM orders o 
     WHERE o.user_id = u.id) as last_order_time
FROM users u;`,
                        explain_sql: `EXPLAIN SELECT u.name, u.email, (SELECT MAX(o.create_time) FROM orders o WHERE o.user_id = u.id) as last_order_time FROM users u;`
                    },
                    {
                        name: 'EXISTS子查询',
                        sql: `-- 子查询示例：查找有订单的用户
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.status = 'completed'
);`,
                        explain_sql: `EXPLAIN SELECT u.name, u.email FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed');`
                    }
                ]
            },

            'materialize': {
                title: '物化操作',
                description: '将子查询结果物化到临时表中',
                examples: [
                    {
                        name: '物化子查询',
                        sql: `-- 物化示例：IN子查询优化
SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
);`,
                        explain_sql: `EXPLAIN SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE total_amount > 1000);`
                    }
                ]
            },

            // 现代SQL特性
            'window_function': {
                title: '窗口函数',
                description: 'MySQL 8.0+支持的窗口函数操作',
                examples: [
                    {
                        name: '排名函数',
                        sql: `-- 窗口函数示例：用户订单金额排名
SELECT 
    user_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_amount DESC) as rank_in_user,
    RANK() OVER (ORDER BY total_amount DESC) as global_rank
FROM orders;`,
                        explain_sql: `EXPLAIN SELECT user_id, total_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_amount DESC) as rank_in_user, RANK() OVER (ORDER BY total_amount DESC) as global_rank FROM orders;`
                    },
                    {
                        name: '累计函数',
                        sql: `-- 窗口函数示例：累计销售额
SELECT 
    DATE(create_time) as order_date,
    SUM(total_amount) as daily_revenue,
    SUM(SUM(total_amount)) OVER (ORDER BY DATE(create_time)) as cumulative_revenue
FROM orders 
GROUP BY DATE(create_time)
ORDER BY order_date;`,
                        explain_sql: `EXPLAIN SELECT DATE(create_time) as order_date, SUM(total_amount) as daily_revenue, SUM(SUM(total_amount)) OVER (ORDER BY DATE(create_time)) as cumulative_revenue FROM orders GROUP BY DATE(create_time) ORDER BY order_date;`
                    }
                ]
            },

            // 过滤操作
            'filter': {
                title: '过滤条件',
                description: '应用WHERE条件过滤数据',
                examples: [
                    {
                        name: '范围过滤',
                        sql: `-- 过滤示例：时间范围查询
SELECT * FROM orders 
WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
  AND total_amount >= 100;`,
                        explain_sql: `EXPLAIN SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31' AND total_amount >= 100;`
                    },
                    {
                        name: '复合条件过滤',
                        sql: `-- 过滤示例：复合条件查询
SELECT * FROM users 
WHERE (status = 'active' OR status = 'premium')
  AND created_at >= '2023-01-01'
  AND email LIKE '%@company.com';`,
                        explain_sql: `EXPLAIN SELECT * FROM users WHERE (status = 'active' OR status = 'premium') AND created_at >= '2023-01-01' AND email LIKE '%@company.com';`
                    }
                ]
            },

            // 去重操作
            'distinct': {
                title: '去重操作',
                description: '移除重复的结果行',
                examples: [
                    {
                        name: '简单去重',
                        sql: `-- 去重示例：获取所有用户的城市列表
SELECT DISTINCT city FROM users 
WHERE city IS NOT NULL;`,
                        explain_sql: `EXPLAIN SELECT DISTINCT city FROM users WHERE city IS NOT NULL;`
                    },
                    {
                        name: '多字段去重',
                        sql: `-- 去重示例：获取用户状态和城市的组合
SELECT DISTINCT status, city 
FROM users 
ORDER BY status, city;`,
                        explain_sql: `EXPLAIN SELECT DISTINCT status, city FROM users ORDER BY status, city;`
                    }
                ]
            },

            // 默认查询块
            'query_block': {
                title: '查询块',
                description: '查询的基本执行单元',
                examples: [
                    {
                        name: '简单查询块',
                        sql: `-- 查询块示例：基本SELECT查询
SELECT id, name, email, status 
FROM users 
WHERE status = 'active' 
ORDER BY name;`,
                        explain_sql: `EXPLAIN FORMAT=JSON SELECT id, name, email, status FROM users WHERE status = 'active' ORDER BY name;`
                    }
                ]
            },

            // 默认情况
            'default': {
                title: '通用操作',
                description: '常见的数据库操作示例',
                examples: [
                    {
                        name: '基础查询',
                        sql: `-- 通用示例：基础数据查询
SELECT * FROM table_name 
WHERE condition = 'value';`,
                        explain_sql: `EXPLAIN SELECT * FROM table_name WHERE condition = 'value';`
                    },
                    {
                        name: '连接查询',
                        sql: `-- 通用示例：表连接查询
SELECT t1.*, t2.related_field
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.foreign_key;`,
                        explain_sql: `EXPLAIN SELECT t1.*, t2.related_field FROM table1 t1 JOIN table2 t2 ON t1.id = t2.foreign_key;`
                    }
                ]
            }
        };
    }

    /**
     * 根据节点类型获取SQL示例
     */
    getExamplesByNodeType(nodeType) {
        return this.examples[nodeType] || this.examples['default'];
    }

    /**
     * 根据节点信息生成上下文相关的SQL示例
     */
    generateContextualExample(node) {
        const baseExample = this.getExamplesByNodeType(node.node_type);
        
        // 尝试从节点标签中提取表名
        const tableName = this.extractTableName(node.label);
        
        // 如果能提取到表名，生成更具体的示例
        if (tableName && tableName !== 'table_name') {
            return this.customizeExampleForTable(baseExample, tableName, node);
        }
        
        return baseExample;
    }

    /**
     * 从节点标签中提取表名
     */
    extractTableName(label) {
        // 尝试匹配各种模式来提取表名
        const patterns = [
            /Table:\s*(\w+)/i,           // "Table: users"
            /on\s+(\w+)\s+using/i,       // "Index scan on orders using"
            /(\w+)\s+using/i,            // "orders using idx_status"
            /lookup\s+on\s+(\w+)/i,      // "Index lookup on users"
            /scan\s+on\s+(\w+)/i,        // "scan on orders"
        ];
        
        for (const pattern of patterns) {
            const match = label.match(pattern);
            if (match && match[1]) {
                return match[1];
            }
        }
        
        return null;
    }

    /**
     * 为特定表定制SQL示例
     */
    customizeExampleForTable(baseExample, tableName, node) {
        const customizedExample = JSON.parse(JSON.stringify(baseExample));
        
        // 替换示例中的通用表名
        customizedExample.examples = customizedExample.examples.map(example => {
            let customizedSql = example.sql.replace(/table_name|table1|table2/g, tableName);
            let customizedExplainSql = example.explain_sql.replace(/table_name|table1|table2/g, tableName);
            
            // 根据节点类型和表名进行更具体的定制
            if (node.node_type === 'index_scan' && node.index_info.key) {
                customizedSql = customizedSql.replace(/idx_\w+/g, node.index_info.key);
                customizedExplainSql = customizedExplainSql.replace(/idx_\w+/g, node.index_info.key);
            }
            
            return {
                ...example,
                sql: customizedSql,
                explain_sql: customizedExplainSql
            };
        });
        
        return customizedExample;
    }

    /**
     * 生成创建测试数据的SQL
     */
    generateTestDataSQL(tableName) {
        const testDataTemplates = {
            'users': `-- 创建用户表测试数据
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('active', 'inactive', 'premium') DEFAULT 'active',
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_city (city)
);

-- 插入测试数据
INSERT INTO users (name, email, status, city) VALUES
('张三', 'zhangsan@example.com', 'active', '北京'),
('李四', 'lisi@example.com', 'premium', '上海'),
('王五', 'wangwu@example.com', 'active', '广州'),
('赵六', 'zhaoliu@example.com', 'inactive', '深圳');`,

            'orders': `-- 创建订单表测试数据
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'active', 'completed', 'cancelled') DEFAULT 'pending',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_create_time (create_time),
    INDEX idx_user_status (user_id, status),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入测试数据
INSERT INTO orders (user_id, total_amount, status, create_time) VALUES
(1, 299.99, 'completed', '2024-01-15 10:30:00'),
(2, 1299.00, 'completed', '2024-01-16 14:20:00'),
(1, 89.50, 'active', '2024-01-17 09:15:00'),
(3, 599.99, 'pending', '2024-01-18 16:45:00');`
        };

        return testDataTemplates[tableName] || `-- 为表 ${tableName} 创建测试数据
CREATE TABLE IF NOT EXISTS ${tableName} (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`;
    }

    /**
     * 生成性能优化建议SQL
     */
    generateOptimizationSQL(node) {
        const optimizations = [];

        // 根据节点类型和警告生成优化建议
        if (node.warnings.includes('Using filesort')) {
            const tableName = this.extractTableName(node.label) || 'your_table';
            optimizations.push(`-- 优化建议：添加排序索引避免文件排序
-- 分析当前查询的ORDER BY字段，创建对应索引
-- 示例：
ALTER TABLE ${tableName} ADD INDEX idx_sort_field (sort_field);`);
        }

        if (node.warnings.includes('Using temporary')) {
            const tableName = this.extractTableName(node.label) || 'your_table';
            optimizations.push(`-- 优化建议：优化GROUP BY避免临时表
-- 确保GROUP BY字段有索引
-- 示例：
ALTER TABLE ${tableName} ADD INDEX idx_group_field (group_field);`);
        }

        if (node.warnings.includes('Full table scan') || node.access_type === 'ALL') {
            const tableName = this.extractTableName(node.label) || 'your_table';
            optimizations.push(`-- 优化建议：添加索引避免全表扫描
-- 分析WHERE条件中的字段，创建合适的索引
-- 示例：
ALTER TABLE ${tableName} ADD INDEX idx_where_field (where_field);`);
        }

        if (node.performance_metrics.cost > 100) {
            optimizations.push(`-- 优化建议：高成本操作优化
-- 1. 检查是否可以通过索引优化
-- 2. 考虑查询重写
-- 3. 分析是否需要分页限制结果集
-- 4. 检查统计信息是否需要更新
ANALYZE TABLE your_table;`);
        }

        return optimizations.join('\n\n');
    }
}

// 导出
window.SqlExampleGenerator = SqlExampleGenerator;
